package org.example.aitest.utils.gen;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.util.Map;
import java.util.LinkedHashMap;
import java.util.stream.Collectors;

@Service
public class DynamicDataService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void insertData(String tableName, Map<String, Object> data) {
        // 获取表元数据以确定列类型
        Map<String, String> columnTypes = getColumnTypes(tableName);

        // 构建SQL
        String columns = String.join(", ", data.keySet());
        String placeholders = String.join(", ", data.keySet().stream()
                .map(k -> "?").collect(Collectors.toList()));

        String sql = "INSERT INTO " + tableName + " (" + columns + ") VALUES (" + placeholders + ")";

        // 使用PreparedStatement安全地设置参数
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement(sql);
                int index = 1;
                for (Map.Entry<String, Object> entry : data.entrySet()) {
                    String columnName = entry.getKey();
                    Object value = entry.getValue();
                    String columnType = columnTypes.get(columnName.toLowerCase());

                    if (value == null || "".equals(value)) {
                        // 根据列类型设置适当的NULL值
                        if (columnType != null && columnType.toLowerCase().contains("decimal")) {
                            ps.setNull(index, java.sql.Types.DECIMAL);
                        } else if (columnType != null && columnType.toLowerCase().contains("int")) {
                            ps.setNull(index, java.sql.Types.INTEGER);
                        } else {
                            ps.setNull(index, java.sql.Types.VARCHAR);
                        }
                    } else {
                        ps.setObject(index, value);
                    }
                    index++;
                }
                return ps;
            }
        });
    }

    // 获取表的列类型信息
    private Map<String, String> getColumnTypes(String tableName) {
        Map<String, String> columnTypes = new LinkedHashMap<>();
        jdbcTemplate.execute((Connection con) -> {
            DatabaseMetaData metaData = con.getMetaData();
            ResultSet columns = metaData.getColumns(null, null, tableName, null);
            while (columns.next()) {
                String columnName = columns.getString("COLUMN_NAME").toLowerCase();
                String columnType = columns.getString("TYPE_NAME");
                columnTypes.put(columnName, columnType);
            }
            return null;
        });
        return columnTypes;
    }
}